package com.maxd.modules.redPacket.dao;

import com.maxd.modules.redPacket.entity.RedPacket;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.Repository;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;
import java.util.Map;

public interface RedPacketRepository extends Repository<RedPacket, Long> {

    @Query(value = "select * from red_packet where user_id=:userId and unix_timestamp(end_time)>=unix_timestamp(now()) order by create_time  ",nativeQuery = true)
    List<RedPacket> selectRedPacketCountByUserId(@Param("userId") Long userId);

    @Query(value = "select * from red_packet where user_id=:userId and unix_timestamp(end_time)>=unix_timestamp(now()) ",nativeQuery = true)
    RedPacket selectRedPacketCountByUserIds(@Param("userId") Long userId);

    @Query(value = "select * from red_packet where user_id=:userId and status=2 ",nativeQuery = true)
    RedPacket selectRedPacketByUserIdAndStatus(@Param("userId") Long userId);

    @Query(value = "select ifnull(sum(money),0.00) from red_packet where date_format(create_time,'%Y-%m-%d')=date_format(now(),'%Y-%m-%d')",nativeQuery = true)
    double getDayMoney();

    @Transactional
    @Modifying
    @Query(value = "update red_packet set money=money+:money,num_people=num_people+1 where red_packet_id=:redPacketId",nativeQuery = true)
    int updateRedPacketTarGetMoneyAdd(@Param("money") Double money, @Param("redPacketId") Long redPacketId);

    @Transactional
    @Modifying
    @Query(value = "update red_packet set money=money-:money,num_people=num_people-1 where red_packet_id=:redPacketId",nativeQuery = true)
    int updateRedPacketTarGetMoneySub(@Param("money") Double money, @Param("redPacketId") Long redPacketId);


    @Query(value = "select p.user_id as userId,p.tar_get_money as tarGetMoney,p.create_time as createTime,u.nick_name as nickName,u.image_url as imageUrl from red_packet p " +
            "left join user_info u on u.id=p.user_id " +
            "where user_id=:userId and status in (2,3) order by create_time desc ",
            countQuery =  "select count(*) from red_packet p " +
                    "left join user_info u on u.id=p.user_id " +
                    "where user_id=:userId and status in (2,3) order by create_time desc "
            ,nativeQuery = true)
    Page<Map<String,Object>> selectRedPacketListByUserIds(Pageable pageable, @Param("userId") Long userId);

    @Query(value = "select * from red_packet where user_id=:userId and status in (2,3) order by create_time desc",
            countQuery = "select count(*) from red_packet where user_id=:userId and status in (2,3)"
            ,nativeQuery = true)
    Page<RedPacket> selectRedPacketListByUserId(Pageable pageable, @Param("userId") Long userId);

    @Query(value = "select * from red_packet where user_id=:userId  order by create_time desc",
            countQuery = "select count(*) from red_packet where user_id=:userId "
            ,nativeQuery = true)
    Page<RedPacket> selectRedPacketListByUserIdV1(Pageable pageable, @Param("userId") Long userId);

    @Query(value = "select * from red_packet where user_id=:userId and status=:status order by create_time desc",
            countQuery = "select count(*) from red_packet where user_id=:userId and status=:status"
            ,nativeQuery = true)
    Page<RedPacket> selectRedPacketListByUserId(Pageable pageable, @Param("userId") Long userId, @Param("status") Integer status);

    @Query(value = "select p.user_id as userId,p.tar_get_money as tarGetMoney,p.create_time as createTime,u.nick_name as nickName,u.image_url as imageUrl from red_packet p " +
            "left join user_info u on u.id=p.user_id " +
            "where p.status in (2,3) order by p.create_time desc ",
            countQuery = "select count(*) from red_packet p " +
                    "left join user_info u on u.id=p.user_id " +
                    "where p.status in (2,3) order by p.create_time desc "
            ,nativeQuery = true)
    Page<Map<String,Object>> selectRedPacketListByStatus(Pageable pageable);

    @Query(value = "select r.* from red_packet r where r.status=:status order by r.create_time desc",
            countQuery = "select count(*) from red_packet where status=:status"
            ,nativeQuery = true)
    Page<RedPacket> selectRedPacketListByStatuss(Pageable pageable, @Param("status") Integer status);

    @Query(value = "select r.* from red_packet r order by r.create_time desc",
            countQuery = "select count(*) from red_packet"
            ,nativeQuery = true)
    Page<RedPacket> selectRedPacketList(Pageable pageable);


}